import pandas as pd
import time
from datetime import datetime
import math

import conf


def create_material(db, material_data):
    """创建物料信息（仅包含指定字段）"""
    try:
        # 当前时间戳（毫秒）
        current_time = int(time.time() * 1000)

        sql = """
        INSERT INTO base_goods 
        (
            goods_type_id, goods_name, goods_code, default_type_name, goods_unit_name,
            goods_spec, goods_properties, default_tax_rate, batch_number,
            purchase_is_allow_return, purchase_is_control_receive, purchase_reference_price,
            purchase_tax, incoming_inspection, sale_reference_price, sale_tax,
            warehouse_is_use_batch, warehouse_is_quality_guarantee_manage, max_inventory,
            min_inventory, warehouse_code, warehouse_name, warehouse_quality_guarantee,
            warehouse_is_allow_negative_stock, creator_id, creator_name, create_time,
            last_updater_id, last_updater_name, last_update_time
        )
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        params = (
            # 基础信息
            material_data['goods_type_id'],  # 物料分类id（必填，需查表）
            material_data['goods_name'],  # 物料名称（必填）
            material_data['goods_code'],  # 物料编码（必填）
            material_data['default_type_name'],  # 基本分类名
            material_data['goods_unit_name'],  # 计量单位名称（必填）
            material_data['goods_spec'],  # 规格型号（默认空字符串）
            # 其他属性
            material_data['goods_properties'],  # 物料属性(0自制/1外购/2虚拟)
            material_data.get('default_tax_rate', 13),  # 默认税率（默认13%）

            # 物料生产信息
            # material_data['product_series_id'],  # 产品系列id（需查表）
            material_data.get('batch_number', 9999),  # 批次数量

            # 采购相关
            material_data.get('purchase_is_allow_return', 1),  # 是否允许退货（1是，0否）
            material_data.get('purchase_is_control_receive', 0),  # 是否控制收货数量（1是，0否）
            material_data['purchase_reference_price'],  # 采购参考单价
            material_data.get('purchase_tax', 13),  # 采购税率
            material_data.get('incoming_inspection', 1),  # 是否来料检验（1是，0否）

            # 销售相关
            material_data['sale_reference_price'],  # 销售参考单价
            material_data.get('sale_tax', 13),  # 销售税率

            # 仓库相关
            material_data.get('warehouse_is_use_batch', 0),  # 是否使用批次号（1是，0否）
            material_data.get('warehouse_is_quality_guarantee_manage', 0),  # 是否进行保质期管理（1是，0否）
            material_data.get('max_inventory', 0),  # 仓库上限
            material_data.get('min_inventory', 0),  # 仓库下限
            material_data['warehouse_code'],  # 默认仓库编码（需查表）
            material_data['warehouse_name'],  # 默认仓库名称
            material_data.get('warehouse_quality_guarantee', 0),  # 保质期
            material_data.get('warehouse_is_allow_negative_stock', 0),  # 是否允许负库存（1是，0否）

            # 创建信息
            material_data['creator_id'],  # 创建人id（必填）
            material_data['creator_name'],  # 创建人姓名（必填）
            current_time,  # 创建时间
            material_data['last_updater_id'],  # 最后修改人id（必填）
            material_data['last_updater_name'],  # 最后修改人姓名（必填）
            current_time  # 最后修改时间
        )
        # print(params)
        # print("#################")
        # print(material_data['product_series_id'])
        count = db.insert_one(sql, params)
        print(f"创建物料 {material_data['goods_code']} 成功，影响行数: {count}")
        return count > 0

    except Exception as e:
        print(f"创建物料失败: {str(e)}")
        db.rollback()
        return False


def create_bom_design_version(db, goods_code):
    """创建BOM设计版本记录"""
    try:
        # 生成时间字符串（年月日时分秒）
        time_str = datetime.now().strftime("%Y%m%d%H%M%S")
        bom_no = f"BOM-{time_str}"
        version = f"v{time_str}"
        op_id = conf.storage_dept_staff["id"]
        op_name = conf.storage_dept_staff["name"]
        op_time = int(time.time() * 1000)

        sql = """
        INSERT INTO bom_design_version 
        (design_project_id, goods_code, bom_no, version, remark, status, use_status,publish_id,publish_name,publish_time,creator_id,creator_name,create_time,last_updater_id,last_updater_name,last_update_time)
        VALUES (%s, %s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
        """
        params = (0, goods_code, bom_no, version, "新增", 1, 1,op_id,op_name,op_time,op_id,op_name,op_time,op_id,op_name,op_time)

        # 插入并返回自增ID
        vid = db.insert_one_return_id(sql, params)
        print(f"创建BOM设计版本 {bom_no} 成功，ID: {vid}")
        return vid

    except Exception as e:
        print(f"创建BOM设计版本失败: {str(e)}")
        db.rollback()
        return None


def create_bom_design_project_goods(db, vid, goods_code, consume_quota, unit, parent_code):
    """创建BOM设计项目物料关联记录"""
    try:
        op_id = conf.storage_dept_staff["id"]
        op_name = conf.storage_dept_staff["name"]
        op_time = int(time.time() * 1000)
        sql = """
        INSERT INTO bom_design_project_goods 
        (vid, goods_code, consume_quota, goods_unit_name, parent_code,sort,creator_id,creator_name,create_time,last_updater_id,last_updater_name,last_update_time)
        VALUES (%s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s)
        """
        params = (vid, goods_code, consume_quota, unit, parent_code,0,op_id,op_name,op_time,op_id,op_name,op_time)

        count = db.insert_one(sql, params)
        print(f"创建BOM项目关联 {vid}-{goods_code} 成功，影响行数: {count}")
        return count > 0

    except Exception as e:
        print(f"创建BOM项目关联失败: {str(e)}")
        db.rollback()
        return False


def process_excel_materials(db, excel_file_path):
    """处理Excel文件，批量导入物料及BOM信息"""
    try:
        # 读取Excel的sheet1
        df = pd.read_excel(excel_file_path, sheet_name='Sheet1',converters={'物料编码': str})
        print(f"成功读取Excel文件，共 {len(df)} 条数据")

        # # 开启事务
        # db.begin()

        for index, row in df.iterrows():
            # 索引index从0开始，对应于表格的第2行
            if index < 199:
                continue
            # 检查索引是否超出实际数据行数
            if index >= 846:
                print("超出199行")
                break
            # 检查索引是否超出实际数据行数
            if index >= len(df):
                print("超出实际数据行数")
                break
            try:
                # 1. 构造物料数据
                material_data = {
                    # 基础必填字段
                    'goods_type': row.get('物料类型（产成品/原材料）', ''),
                    'goods_name': row.get('物料名称', ''),
                    'goods_code': row.get('物料编码', ''),
                    'default_type_name': row.get('基本分类名', ''),
                    'goods_unit_name': row.get('计量单位名称', ''),
                    'goods_spec': row.get('规格型号', ''),
                    'goods_properties':row.get('物料属性(0自制/1外购/2虚拟)',''),

                    'product_series_id':row.get('产品系列id',''),

                    'purchase_reference_price': 0 if math.isnan(row.get('采购参考单价', float('nan'))) else row.get('采购参考单价', 0),
                    'sale_reference_price': 0 if math.isnan(row.get('销售参考单价', float('nan'))) else row.get('销售参考单价', 0),

                    'warehouse_code':row.get('默认仓库编码',''),
                    'warehouse_name':row.get('默认仓库名称',''),

                    # 其他字段
                    'creator_id': conf.storage_dept_staff['id'],
                    'creator_name': conf.storage_dept_staff['name'],
                    'last_updater_id': conf.storage_dept_staff['id'],
                    'last_updater_name': conf.storage_dept_staff['name']
                }
                # 进行映射
                type_mapping = {
                    '成品': 1,
                    '原材料': 2
                }
                material_data['goods_type_id'] = type_mapping.get(material_data['goods_type'], 0)

                # # 校验必填字段
                # required_fields = ['goods_type_id','goods_name', 'goods_code', 'default_type_name', 'goods_unit_name', 'goods_spec', 'goods_properties',
                #                    'product_series_id', 'warehouse_code', 'warehouse_name']
                # if not all(material_data[field] for field in required_fields):
                #     print(material_data['goods_type_id'])
                #     print(material_data['goods_name'])
                #     print(material_data['goods_code'])
                #     print(material_data['default_type_name'])
                #     print(material_data['goods_unit_name'])
                #     print(material_data['goods_spec'])
                #     print(material_data['goods_properties'])
                #     print(material_data['product_series_id'])
                #     print(material_data['warehouse_code'])
                #     print(material_data['warehouse_name'])
                #     print(f"第 {index + 1} 行数据缺少必填字段，跳过处理")
                #     continue

                # 物料类型特定校验
                goods_type = material_data['goods_type']
                if goods_type == '成品':
                    # 产成品必须有销售参考单价
                    if material_data['sale_reference_price'] in (None, '', 0):
                        print("产成品的销售参考单价不能为空或为0")
                elif goods_type == '原材料':
                    # 原材料必须有采购参考单价
                    if material_data['purchase_reference_price'] in (None, '', 0):
                        print("原材料的采购参考单价不能为空或为0")
                elif goods_type:
                    print(f"不支持的物料类型: {goods_type}")
                else:
                    print("物料类型必须指定为'产成品'或'原材料'")


                # 2. 新增物料
                if not create_material(db, material_data):
                    print(f"第 {index + 1} 行物料 {material_data['goods_code']} 创建失败，跳过后续处理")
                    continue

                # 3. 处理产成品的BOM信息
                if goods_type == '成品':
                    print("这是产成品")
                    # 3.1 创建BOM设计版本
                    vid = create_bom_design_version(db, material_data['goods_code'])
                    if not vid:
                        print(f"第 {index + 1} 行物料BOM版本创建失败，跳过BOM项目处理")
                        continue

                    # 3.2 新增当前物料的BOM项目关联
                    current_unit = row.get('物料计量单位','')
                    if not create_bom_design_project_goods(db, vid, material_data['goods_code'], 1, current_unit,""):
                        print(f"第 {index + 1} 行物料主BOM项目创建失败")
                        continue

                    # 3.3 处理子物料信息，注意使用中文逗号分隔还是英文逗号分隔
                    child_codes = str(row.get('子物料编码', '')).split('，')
                    child_quotas = str(row.get('消耗定额', '')).split('，')
                    child_units = str(row.get('子物料消耗单位', '')).split('，')

                    # 校验子物料数据长度一致性
                    if len(child_codes) != len(child_quotas) or len(child_codes) != len(child_units):
                        print(f"第 {index + 1} 行子物料数据格式不一致，跳过子物料处理")
                        continue

                    # 循环处理子物料
                    for i in range(len(child_codes)):
                        code = child_codes[i].strip()
                        quota = child_quotas[i].strip()
                        unit = child_units[i].strip()

                        if not all([code, quota, unit]):
                            print(f"第 {index + 1} 行子物料 {i + 1} 数据不完整，跳过")
                            continue

                        # 转换消耗定额为数值
                        try:
                            quota = float(quota)
                        except ValueError:
                            print(f"第 {index + 1} 行子物料 {i + 1} 消耗定额格式错误，跳过")
                            continue

                        # 新增子物料BOM关联
                        create_bom_design_project_goods(
                            db, vid, code, quota, unit, parent_code=material_data['goods_code']
                        )

                print(f"第 {index + 1} 行数据处理完成")

            except Exception as e:
                print(f"第 {index + 1} 行数据处理异常: {str(e)}，继续处理下一行")
                continue

        # 提交事务
        db.commit()
        print("所有数据处理完成，事务已提交")
        return True

    except Exception as e:
        print(f"Excel处理主流程失败: {str(e)}")
        db.rollback()
        return False